package com.kcsj.dao;

import com.kcsj.domain.Count;
import com.kcsj.domain.DelayedApplication;
import com.kcsj.domain.Order;
import com.kcsj.service.OrderService;
import com.kcsj.util.Condition;
import com.kcsj.util.JdbcHelper;

import java.sql.*;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

/**
 * @author 傅立贤
 * 29th Nov. 2020
 */
public class DelayedApplicationDao {
    private static final DelayedApplicationDao delayedApplicationDao = new DelayedApplicationDao();
    private DelayedApplicationDao() {
    }

    public static DelayedApplicationDao getInstance() {
        return delayedApplicationDao;
    }
    /**
     * @author 傅立贤
     * @param conn    数据库连接对象
     * @param delayedApplication 增加对象
     * @return rowsAffected 影响的行数
     * @throws SQLException
     */
    public boolean add(DelayedApplication delayedApplication, Connection conn) throws SQLException {
        //预编译方式进行增加数据
        String add_sql = "INSERT INTO DelayedApplication(orderNo,delayDays,delayOfMoney,applicationStatus)" +
                " value (?,?,?,?)";
        PreparedStatement preparedStatement = conn.prepareStatement(add_sql);
        preparedStatement.setString(1,delayedApplication.getOrder().getOrderNo());
        preparedStatement.setInt(2,delayedApplication.getDelayDays());
        preparedStatement.setDouble(3,delayedApplication.getDelayOfMoney());
        preparedStatement.setString(4,delayedApplication.getApplicationStatus());
        //如果执行影响行数大于0，则增加成功
        int rowsAffected = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return rowsAffected>0;
    }

    /**
     * @author 傅立贤
     * @param conn    数据库连接对象

     * @param conditionList 条件查找
     * @return delayedApplications 查找的结果
     * @throws SQLException
     */
    public Collection<DelayedApplication> findAll
                    (List<Condition> conditionList, Connection conn)
                        throws SQLException {
        ArrayList<DelayedApplication> delayedApplications = new ArrayList<>();
        //预编译方式进行查询
        StringBuilder sql = new StringBuilder("select * from delayedApplication ");
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql.append(whereClause);
        }
        PreparedStatement preparedStatement = conn.prepareStatement(sql.toString());
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            String orderNo = resultSet.getString("orderNo");
            Order order = OrderService.getInstance().find(orderNo);
            int delayDays = resultSet.getInt("delayDays");
            Timestamp timestamp = resultSet.getTimestamp("applyDate");
            double delayOfMoney = resultSet.getDouble("delayOfMoney");
            DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            String applicationStatus = resultSet.getString("applicationStatus");
            DelayedApplication delayedApplication = new DelayedApplication(order, delayDays,
                    timestamp.toLocalDateTime().format(dtf), delayOfMoney,applicationStatus);
            delayedApplications.add(delayedApplication);
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return delayedApplications;
    }
    /**
     * @author 傅立贤
     * @Method update 更新延期申请状态
     * @param conn    数据库连接对象
     * @param delayedApplication 延期申请
     * @return rowsAffected 影响的行数
     * @throws SQLException
     */
    public boolean update(DelayedApplication delayedApplication, Connection conn) throws SQLException {
        String update_sql = "UPDATE delayedApplication SET applicationStatus = ? WHERE orderNo = ?";
        PreparedStatement pstmt = conn.prepareStatement(update_sql);
        pstmt.setString(1,delayedApplication.getApplicationStatus());
        pstmt.setString(2,delayedApplication.getOrder().getOrderNo());
        int rowsAffected = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return rowsAffected>0;
    }

    /**
     * @author 傅立贤
     * @param orderNo 查找的orderNo 订单号
     * @return delayedApplication 查找到的延期申请
     * @throws SQLException
     */
    public DelayedApplication find(String orderNo,Connection conn) throws SQLException {
        //sql语句
        String find_sql = "SELECT * FROM delayedApplication where orderNo=?";
        PreparedStatement pstmt = conn.prepareStatement(find_sql);
        pstmt.setString(1,orderNo);
        ResultSet resultSet = pstmt.executeQuery();
        DelayedApplication delayedApplication = null;
        if (resultSet.next()) {
            double delayOfMoney  = resultSet.getDouble("delayOfMoney");
            Integer delayDays  = resultSet.getInt("delayDays");
            Order order = OrderService.getInstance().find(orderNo);
            String applicationStatus  = resultSet.getString("applicationStatus");
            Timestamp applyDate_timestamp = resultSet.getTimestamp("applyDate");
            DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            delayedApplication = new DelayedApplication(order,delayDays,
                    applyDate_timestamp.toLocalDateTime().format(dtf),delayOfMoney,applicationStatus);
        }
        JdbcHelper.close(pstmt);
        return delayedApplication;
    }

    public Collection<Count> countByStatus (Connection connection) throws SQLException {
        ArrayList<Count> counts = new ArrayList<>();
        //sql语句
        String find_sql = "SELECT applicationStatus,count(*) from " +
                "delayedApplication GROUP BY applicationStatus";
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        ResultSet resultSet = pstmt.executeQuery();
        while (resultSet.next()) {
            String status = resultSet.getString(1);
            int countOfStatus = resultSet.getInt(2);
            Count count = new Count(status,countOfStatus);
            counts.add(count);
        }
        return counts;
    }

    public Collection<Count> countByDay (Connection connection) throws SQLException {
        ArrayList<Count> counts = new ArrayList<>();
        //sql语句
        String find_sql = "select DATE_FORMAT(applyDate,'%Y-%m-%d') days,count(*) " +
                "from delayedapplication  group by days";
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        ResultSet resultSet = pstmt.executeQuery();
        while (resultSet.next()) {
            String status = resultSet.getString(1);
            int countOfStatus = resultSet.getInt(2);
            Count count = new Count(status,countOfStatus);
            counts.add(count);
        }
        return counts;
    }
    /*
    * 删除
    * */
    public boolean delete(String orderNo,Connection conn) throws SQLException {
        String sql = "delete from delayedApplication where orderNo=?";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1,orderNo);
        int rowsAffected = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement);
        return rowsAffected>0;
    }
}
